PostgreSQL 数据库性能 RUM 索引
1 背景知识
RUM 扩展模块提供 RUM 索引的访问方法。是基于 PostgreSQL GIN 索引 索引访问方法的代码上实现的。
1.1 GIN 索引全文索引的问题
GIN 索引可以使用 tsvector 和 tsquery 进行快速全文索引。但有许多问题：
1.1.1 排序慢
GIN 索引需要关键词的位置信息作为排序的依据，而GIN索引并不存储词素的位置。所以，在索引扫描后，还需要通过扫描表数据获取位置信息。
1.1.2 短语搜索慢
此问题和上一个问题相同，扫描表数据获取位置信息之后才能执行短语搜索。
1.1.3 timestamp 排序慢
此问题和上一个问题相同，需要扫描表数据获取位置信息之后才能排序
1.2 RUM 索引解决的问题
RUM 索引 通过添加 词位 的位置信息和时间提高查询性能。
2 全文搜索
关于 PostgreSQL 服务器编程 全文搜索，请参考 PostgreSQL 全文搜索 词典、PostgreSQL 全文搜索 解析器、PostgreSQL 全文搜索 简介。
3 环境准备
3.1 安装RUM 扩展
su - postgres
cd /soft
git clone https://github.com/postgrespro/rum
cd rum
make USE_PGXS=1
make USE_PGXS=1 install
make USE_PGXS=1 installcheck
psql testdb -c "CREATE EXTENSION rum;"
Warning
如果无法下载，请手动上传rum 索引源码包。
3.2 准备测试数据
请导入 PostgreSQL Sakila示例库，本章需要使用 film 表实验。
3.3 匹配示例
- 包含
Woman和Forensic之间差三个单词的短语文档。
SELECT title
FROM film
WHERE to_tsvector(description) @@ to_tsquery('Woman <3> Forensic');
title
------------
WAIT CIDER
(1 row)
to_tsquery 函数能将文本格式为 tsquery 类型。
- 同时包含
Woman和Forensic文档有10个。
SELECT title
FROM film
WHERE to_tsvector(description) @@ to_tsquery('Woman & Forensic');
title
-------------------
WAIT CIDER
PACKER MADIGAN
COMMAND DARLING
DOCTOR GRAIL
TEMPLE ATTRACTION
MINE TITANS
SNATCH SLIPPER
TROUBLE DATE
MARRIED GO
INFORMER DOUBLE
(10 rows)
4 短语查询性能
4.1 GIN 索引
4.1.1 创建 GIN 索引
DROP INDEX film_fulltext_idx;
CREATE INDEX film_fulltext_idx on film USING GIN(fulltext);
4.1.2 查看GIN索引执行计划
通过索引返回1条记录，也就是索引没有包含位置的信息，需要访问表数据。
EXPLAIN ANALYZE
SELECT title
FROM film
WHERE fulltext @@ to_tsquery('Woman <3> Forensic');
QUERY PLAN
-------------------------------------------------
Bitmap Heap Scan on film (cost=13.29..44.06 rows=10 width=15) (actual time=0.063..0.113 rows=1 loops=1)
Recheck Cond: (fulltext @@ to_tsquery('Woman <3> Forensic'::text))
Rows Removed by Index Recheck: 9
Heap Blocks: exact=9
-> Bitmap Index Scan on film_fulltext_idx (cost=0.00..13.29 rows=10 width=0) (actual time=0.047..0.047 rows=10 loops=1)
Index Cond: (fulltext @@ to_tsquery('Woman <3> Forensic'::text))
Planning Time: 0.210 ms
Execution Time: 0.135 ms
(8 rows)
4.2 RUM 索引
4.2.1 创建 RUM 索引
DROP INDEX film_fulltext_idx;
CREATE INDEX film_fulltext_idx ON film USING RUM(fulltext);
4.2.2 查看执行计划
可以看到索引返回的记录就一条，也就是说明索引包含有位置信息。
EXPLAIN ANALYZE
SELECT title
FROM film
WHERE fulltext @@ to_tsquery('Woman <3> Forensic');
QUERY PLAN
------------------------------------------------
Bitmap Heap Scan on film (cost=21.79..52.56 rows=10 width=15) (actual time=0.072..0.072 rows=1 loops=1)
Recheck Cond: (fulltext @@ to_tsquery('Woman <3> Forensic'::text))
Heap Blocks: exact=1
-> Bitmap Index Scan on film_fulltext_idx (cost=0.00..21.79 rows=10 width=0) (actual time=0.066..0.066 rows=1 loops=1)
Index Cond: (fulltext @@ to_tsquery('Woman <3> Forensic'::text))
Planning Time: 0.280 ms
Execution Time: 0.097 ms
(7 rows)
5 相似似度排名性能
5.1 GIN 索引
5.1.1 创建 GIN 索引
DROP INDEX film_fulltext_idx;
CREATE INDEX film_fulltext_idx on film USING GIN(fulltext);
5.1.2 查看GIN索引执行计划
通过索引返回7条记录，也就是索引没有包含位置的信息，需要访问表数据。
EXPLAIN ANALYZE
SELECT title ,fulltext <=> to_tsquery('Woman & Forensic') AS rank
FROM film
WHERE fulltext @@ to_tsquery('Woman & Forensic')
ORDER BY rank DESC
LIMIT 10;
QUERY PLAN
----------------------------------------------------------
Limit (cost=46.75..46.78 rows=10 width=19) (actual time=0.161..0.166 rows=10 loops=1)
-> Sort (cost=46.75..46.78 rows=10 width=19) (actual time=0.156..0.161 rows=10 loops=1)
Sort Key: (ts_rank(fulltext, to_tsquery('Woman & Forensic'::text))) DESC
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on film (cost=13.29..46.58 rows=10 width=19) (actual time=0.082..0.140 rows=10 loops=1)
Recheck Cond: (fulltext @@ to_tsquery('Woman & Forensic'::text))
Heap Blocks: exact=9
-> Bitmap Index Scan on film_fulltext_idx (cost=0.00..13.29 rows=10 width=0) (actual time=0.055..0.059 rows=10 loops=1)
Index Cond: (fulltext @@ to_tsquery('Woman & Forensic'::text))
Planning Time: 0.513 ms
Execution Time: 0.203 ms
(11 rows)
5.2 RUM 索引
5.2.1 创建 RUM 索引
DROP INDEX film_fulltext_idx;
CREATE INDEX film_fulltext_idx ON film USING RUM(fulltext);
5.2.2 查看执行计划
可以看到索引返回的记录就一条，也就是索引包含有位置信息。
EXPLAIN ANALYZE
SELECT title ,fulltext <=> to_tsquery('Woman & Forensic') AS rank
FROM film
WHERE fulltext @@ to_tsquery('Woman & Forensic')
ORDER BY rank DESC
LIMIT 10;
QUERY PLAN
-------------------------------------------------------
Limit (cost=55.25..55.28 rows=10 width=19) (actual time=0.136..0.139 rows=10 loops=1)
-> Sort (cost=55.25..55.28 rows=10 width=19) (actual time=0.135..0.136 rows=10 loops=1)
Sort Key: (ts_rank(fulltext, to_tsquery('Woman & Forensic'::text))) DESC
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on film (cost=21.79..55.08 rows=10 width=19) (actual time=0.066..0.125 rows=10 loops=1)
Recheck Cond: (fulltext @@ to_tsquery('Woman & Forensic'::text))
Heap Blocks: exact=9
-> Bitmap Index Scan on film_fulltext_idx (cost=0.00..21.79 rows=10 width=0) (actual time=0.048..0.048 rows=10 loops=1)
Index Cond: (fulltext @@ to_tsquery('Woman & Forensic'::text))
Planning Time: 0.212 ms
Execution Time: 0.168 ms
(11 rows)